package JDBC的复习;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;

public class JDBC2 {
    public static void main(String[] args) throws Exception {
        /*//注册mysql驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");*/

        //使用数据库连接池
        Properties pop=new Properties();
        //获取配置文件位置
        pop.load(new InputStreamReader(new FileInputStream("D:\\idea2022\\Engineerclass\\druid.properties")));
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pop);
        //获取连接
        Connection connection = dataSource.getConnection();
        //执行sql语句
        /*String sql = "create table tb1('id' int not null auto_increment primary key, 'name' varchar(20) not null, 'sex' varchar(10) not null)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.executeUpdate();*/

        //添加
        boolean insert = insert(connection);
        System.out.println(insert);

        PreparedStatement preparedStatement1 = connection.prepareStatement("select * from tb1");
        ResultSet resultSet = preparedStatement1.executeQuery();
       while (resultSet.next()){
           //接收数据
           int id = resultSet.getInt("id");
           String name = resultSet.getString("name");
           String age = resultSet.getString("age");
           String sex = resultSet.getString("sex");
           System.out.println("id:"+id+",name:"+name+",sex:"+sex);
       }
    }
    /**
     * 给数据库添加数据的方法
     *分析:
     * id是数据库自增的
     *1.sql语句是:insert into tb1(name, age, sex) values (?,?,?,);
     *返回值为boolean;
     */
    public static boolean insert(Connection conn) throws SQLException {
        Scanner scanner=new Scanner(System.in);
        String name=scanner.next();
        int age=scanner.nextInt();
        String sex=scanner.next();

        String sql = "insert into tb1(name, age, sex) values (?,?,?);";
        //定义执行sql对象
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,name);
        preparedStatement.setInt(2,age);
        preparedStatement.setString(3,sex);
        int i = preparedStatement.executeUpdate();
        return i==1;
    }
}
